/*
 * Copyright (c) 2009 Tim Mead
 *
 * Permission is hereby granted, free of charge, to any person
 * obtaining a copy of this software and associated documentation
 * files (the "Software"), to deal in the Software without
 * restriction, including without limitation the rights to use,
 * copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the
 * Software is furnished to do so, subject to the following
 * conditions:
 *
 * The above copyright notice and this permission notice shall be
 * included in all copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
 * EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
 * OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
 * NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
 * HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
 * WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
 * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
 * OTHER DEALINGS IN THE SOFTWARE.
 *
 */

/**
 * Implementation of DAO methods for the 'geocode' database table.
 */
package com.misoneism.fire.database.dao
{
	import com.misoneism.fire.database.tuples.Geocode;
	
	import flash.data.SQLConnection;
	import flash.data.SQLResult;
	import flash.data.SQLStatement;

	public class GeocodeDAO implements IGeocodeDAO
	{
		private var dbConnection:SQLConnection;
		
		public function GeocodeDAO(dbConnection:SQLConnection)
		{
			this.dbConnection = dbConnection;
		}

		/**
		 * Get a particular Geocode record that matches the specified
		 * address. If no matching record could be found, then null is
		 * returned.
		 */
		public function getGeocode(address:String):Geocode
		{
			var sql:String =
				"SELECT id, address, latitude, longitude, create_date as createDate " + 
				"FROM geocode " + 
				"WHERE " + 
				"  address = :address";
				
			// Build up and execute the statement
			var statement:SQLStatement = new SQLStatement();
			statement.sqlConnection = dbConnection;
			statement.text = sql;
			statement.itemClass = Geocode;
			statement.parameters[":address"] = processAddress(address);
			statement.execute();
			
			// Process the results
			var result:SQLResult = statement.getResult();
			if ((result.data == null) || (result.data.length == 0))
				return null;
			
			return result.data[0] as Geocode;
		}
		
		/**
		 * Insert a new Geocode record, with the specified details. The
		 * date of creation of the record is computed automatically. This
		 * method does not check to see if a record of the same address
		 * already exists.
		 */
		public function insertGeocode(address:String, latitude:Number, longitude:Number):int
		{
			var sql:String =
				"INSERT INTO geocode (address, latitude, longitude, create_date) " + 
				"values(:address, :latitude, :longitude, :create_date)";
				
			// Build up and execute the statement
			dbConnection.begin();
			var statement:SQLStatement = new SQLStatement();
			statement.sqlConnection = dbConnection;
			statement.text = sql;
			statement.parameters[":address"] = processAddress(address);
			statement.parameters[":latitude"] = latitude;
			statement.parameters[":longitude"] = longitude;
			statement.parameters[":create_date"] = new Date();
			statement.execute();
			dbConnection.commit();
			
			return 0;
		}
		
		/**
		 * Preprocess the specified address, making it database-ready.
		 */
		private function processAddress(address:String):String
		{
			// Keep everything case-insensitive
			var processedString:String = address.toLowerCase();
			
			// Remove all commas
			processedString = processedString.replace(",", "");
			
			// Compact all whitespace
			processedString = processedString.replace("\s+", " ");
			
			return processedString;
		}
	}
}